/*
2016-05-05 Add minimum_consecutive_reservations to courses.sql

	Written by Patrick Headley, Linx Consulting, Inc.
	Adds minimum_consecutive_reservations.
*/
BEGIN TRANSACTION;

-- Drop dependencies.
DROP TRIGGER courses_stamp ON courses;
ALTER TABLE bookings DROP CONSTRAINT fk_bookings_courses;
ALTER TABLE buyer_to_courses_taken_map DROP CONSTRAINT buyer_to_courses_taken_map_buyer_course_id;
ALTER TABLE buyer_to_courses_taken_map DROP CONSTRAINT buyer_to_courses_taken_map_courses_taken_course_id;
ALTER TABLE courses_resources DROP CONSTRAINT fk_courses_resources_courses;
ALTER TABLE courses_resources_reservations DROP CONSTRAINT fk_courses_resourses_reservations_courses;
ALTER TABLE coursestaken DROP CONSTRAINT coursestaken_courses_fkey;
ALTER TABLE emailtemplates DROP CONSTRAINT emailtemplates_course_id_fkey;
DROP INDEX courses_courseid_ux;
ALTER TABLE courses DROP CONSTRAINT courses_pkey;




DROP VIEW cvw_bookings_courses_by_email;
DROP VIEW cvw_bookings_reservations_by_group_id;
DROP VIEW cvw_bookings_cancellation_message;
DROP VIEW cvw_booking_available_whybuy;
DROP VIEW cvw_bookings_reservations_enabled;
DROP VIEW cvw_bookings_resource_usage_by_course;
DROP VIEW cvw_bookings_resource_usage_by_resource_type;
DROP VIEW cvw_bookings_resource_usage_by_booking;
DROP VIEW cvw_bookings_user_defaults;
DROP VIEW cvw_bookings_resources_max_daily_count;
DROP VIEW cvw_bookings_resources_count_by_booking;
DROP VIEW cvw_bookings_confirmation_message;
DROP VIEW cvw_bookings_resources_courses_enabled;


-- Rename the original table.
ALTER TABLE courses RENAME TO courses_original;

-- Create the new table.
CREATE TABLE courses
(
  record_id serial NOT NULL, -- primary key from sequence
  courseid character varying(30) NOT NULL, -- former primary key, still always unique
  description character varying(100) NOT NULL, -- course name
  coursetype character varying(30) NOT NULL, -- ASA, PRODUCT, COURSE
  default_email_id integer, -- email template to use, deprecated, use emailid or batchemailid
  displayorder integer NOT NULL, -- orders popup and lists of courses
  registertaken integer NOT NULL DEFAULT 0, -- should hika transfer create a coursetaken row
  bookings_product integer NOT NULL DEFAULT 0, -- link to scheduling system
  minimum_consecutive_reservations integer NOT NULL DEFAULT 1,  -- Set to the minumum number of consecutive reservations a person must make.
  printlabel integer NOT NULL, -- does Jim need to print a label
  emailid integer, -- reference to emailtemplate.emailid, zero if batchemailid is set
  batchemailid integer, -- reference to emailtemplatebatch.batchid, zero if batchemailid is set is set
  lastmodified timestamp without time zone NOT NULL, -- set by trigger
  CONSTRAINT courses_pkey PRIMARY KEY (record_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE courses
  OWNER TO "vicsailBE";
COMMENT ON COLUMN courses.record_id IS 'primary key from sequence';
COMMENT ON COLUMN courses.courseid IS 'former primary key, still always unique';
COMMENT ON COLUMN courses.description IS 'course name';
COMMENT ON COLUMN courses.coursetype IS 'ASA, PRODUCT, COURSE';
COMMENT ON COLUMN courses.default_email_id IS 'email template to use, deprecated, use emailid or batchemailid';
COMMENT ON COLUMN courses.displayorder IS 'orders popup and lists of courses';
COMMENT ON COLUMN courses.registertaken IS 'should hika transfer create a coursetaken row';
COMMENT ON COLUMN courses.bookings_product IS 'link to scheduling system';
COMMENT ON COLUMN courses.printlabel IS 'does Jim need to print a label';
COMMENT ON COLUMN courses.emailid IS 'reference to emailtemplate.emailid, zero if batchemailid is set';
COMMENT ON COLUMN courses.batchemailid IS 'reference to emailtemplatebatch.batchid, zero if batchemailid is set is set';
COMMENT ON COLUMN courses.lastmodified IS 'set by trigger';

-- Add the origina records into the new table.
INSERT INTO courses
	( record_id,
	  courseid,
	  description,
	  coursetype,
	  default_email_id,
	  displayorder,
	  registertaken,
	  bookings_product,
	  printlabel,
	  emailid,
	  batchemailid,
	  lastmodified)
SELECT record_id,
	  courseid,
	  description,
	  coursetype,
	  default_email_id,
	  displayorder,
	  registertaken,
	  bookings_product,
	  printlabel,
	  emailid,
	  batchemailid,
	  lastmodified
FROM courses_original;	  

-- Set the initial value for the sequence.
SELECT setval('courses_record_id_seq', (SELECT MAX(record_id) FROM courses));

-- Drop the original table.
DROP TABLE courses_original;


-- Index: courses_courseid_ux
CREATE UNIQUE INDEX courses_courseid_ux
  ON courses
  USING btree
  (courseid COLLATE pg_catalog."default");


-- Trigger: courses_stamp on courses
CREATE TRIGGER courses_stamp
  BEFORE INSERT OR UPDATE
  ON courses
  FOR EACH ROW
  EXECUTE PROCEDURE get_timestamp();


-- Add dependents back.

ALTER TABLE bookings
  ADD CONSTRAINT fk_bookings_courses FOREIGN KEY (course_id)
      REFERENCES courses (courseid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;  

ALTER TABLE buyer_to_courses_taken_map
  ADD CONSTRAINT buyer_to_courses_taken_map_buyer_course_id FOREIGN KEY (buyer_course_id)
      REFERENCES courses (courseid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE buyer_to_courses_taken_map
  ADD CONSTRAINT buyer_to_courses_taken_map_courses_taken_course_id FOREIGN KEY (courses_taken_course_id)
      REFERENCES courses (courseid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE courses_resources
  ADD CONSTRAINT fk_courses_resources_courses FOREIGN KEY (course_id)
      REFERENCES courses (courseid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE courses_resources_reservations
  ADD CONSTRAINT fk_courses_resourses_reservations_courses FOREIGN KEY (course_id)
      REFERENCES courses (courseid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE coursestaken
  ADD CONSTRAINT coursestaken_courses_fkey FOREIGN KEY (courseid)
      REFERENCES courses (courseid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE emailtemplates
  ADD CONSTRAINT emailtemplates_course_id_fkey FOREIGN KEY (course_id)
      REFERENCES courses (courseid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
      

CREATE OR REPLACE VIEW cvw_bookings_resources_courses_enabled AS 
 SELECT b2.record_id AS booking_record_id,
    br.record_id AS bookings_resources_record_id,
    br.resource_record_id,
    b2.course_id,
    r.resource_type_code,
    b2.location_name,
    b2.booking_date,
    b2.start_time,
    b2.end_time
   FROM bookings b2
     JOIN bookings_resources br ON b2.record_id = br.bookings_record_id
     JOIN courses c2 ON b2.course_id::text = c2.courseid::text
     JOIN resources r ON br.resource_record_id = r.record_id
  WHERE c2.coursetype::text = 'COURSE'::text AND c2.bookings_product = (-1) AND b2.date_canceled IS NULL;

ALTER TABLE cvw_bookings_resources_courses_enabled
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_resources_courses_enabled
  IS 'Returns bookings records only for courses with additional
  booking information for active (not canceled)
  booking records.

  Used by cvw_bookings_available_why_buy to make it easier to read but can be 
  used anywhere you need a list of course records that are set up for use
  in the booking system.';

CREATE OR REPLACE VIEW cvw_bookings_confirmation_message AS 
 SELECT b.record_id AS booking_record_id,
    c.description AS course_description,
    b.location_name,
    b.booking_date,
    b.start_time
   FROM bookings b
     JOIN courses c ON b.course_id::text = c.courseid::text;

ALTER TABLE cvw_bookings_confirmation_message
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_confirmation_message
  IS 'This view is used by the booking site when confirming that the user wants to make a reservation.';

CREATE OR REPLACE VIEW cvw_bookings_resources_count_by_booking AS 
 SELECT b.record_id,
    b.course_id,
    b.location_name,
    b.booking_date,
    b.start_time,
    r.resource_type_code,
    count(br.record_id) AS count_resources,
    b.date_canceled
   FROM bookings b
     JOIN courses c ON b.course_id::text = c.courseid::text
     LEFT JOIN bookings_resources br ON b.record_id = br.bookings_record_id
     LEFT JOIN resources r ON br.resource_record_id = r.record_id
  WHERE c.coursetype::text = 'WB'::text AND c.bookings_product = (-1)
  GROUP BY b.record_id, b.course_id, b.location_name, b.booking_date, b.start_time, r.resource_type_code;

ALTER TABLE cvw_bookings_resources_count_by_booking
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_resources_count_by_booking
  IS 'Returns descriptive fields for each booking plus the count of resources available for each. One record is
 returned per related record in bookings_resources.';

CREATE OR REPLACE VIEW cvw_bookings_resources_max_daily_count AS 
 SELECT cb.course_id,
    cb.location_name,
    cb.resource_type_code,
    cb.booking_date,
    max(cb.count_resources) AS max_count_resources
   FROM cvw_bookings_resources_count_by_booking cb
  WHERE cb.date_canceled IS NULL
  GROUP BY cb.course_id, cb.location_name, cb.resource_type_code, cb.booking_date;

ALTER TABLE cvw_bookings_resources_max_daily_count
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_resources_max_daily_count
  IS 'Returns the maximum number of resources for any one of the bookings with a specific date. This
view is used specifically by the daily calendar in the booking system to determine how many time slots to create,
even if there aren''t the same number of time slots at each booking time for the day.

Only includes bookings that haven''t been canceled.';

CREATE OR REPLACE VIEW cvw_bookings_user_defaults AS 
 SELECT p.peopleid,
    c1.courseid,
    c1.resource_location_name,
    c1.resource_type_code
   FROM people p,
    ( SELECT DISTINCT ON (ct.peopleid) ct.peopleid,
            ct.courseid,
            r.resource_location_name,
            r.resource_type_code,
            ct.dateregistered
           FROM coursestaken ct
             JOIN courses c ON ct.courseid::text = c.courseid::text
             JOIN courses_resources cr ON c.courseid::text = cr.course_id::text
             JOIN resources r ON cr.resource_record_id = r.record_id
          ORDER BY ct.peopleid, ct.dateregistered DESC) c1
  WHERE p.peopleid = c1.peopleid AND (c1.courseid::text IN ( SELECT u.course_id
           FROM cvw_bookings_count_course_reservations_unused u
          WHERE u.people_id = p.peopleid))
  ORDER BY p.peopleid, c1.dateregistered DESC;

ALTER TABLE cvw_bookings_user_defaults
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_user_defaults
  IS 'Written by Patrick Headley, Linx Consulting, Inc.
	Returns defaults for the user in the calendar selector dropdown 
	controls in the booking system for when the user first logs in.
	The defaults are selected by returning a course and resource data for
	the user, with the most recent coursestaken.registered date first
	and preferrably with a course that is available for the user to book.';

CREATE OR REPLACE VIEW cvw_bookings_resource_usage_by_booking AS 
 SELECT resource.booking_record_id,
    resource.course_id,
    resource.course_description,
    resource.location_name,
    resource.resource_type_code,
    resource.booking_date,
    resource.start_time,
    resource.end_time,
    resource.resource_record_id,
    resource.max_course_reservations,
    COALESCE(reservation.total_reservations_used, 0::bigint) AS total_reservations_used,
    COALESCE(resource.max_course_reservations, 0) - COALESCE(reservation.total_reservations_used, 0::bigint) AS total_reservations_available
   FROM ( SELECT b.record_id AS booking_record_id,
            b.course_id,
            c.description AS course_description,
            c.coursetype,
            c.bookings_product,
            b.location_name,
            r.resource_type_code,
            b.booking_date,
            b.start_time,
            b.end_time,
            br.record_id AS bookings_resources_record_id,
            r.record_id AS resource_record_id,
            r.max_course_reservations
           FROM resources r
             LEFT JOIN (bookings_resources br
             JOIN bookings b ON br.bookings_record_id = b.record_id) ON r.record_id = br.resource_record_id
             JOIN courses c ON b.course_id::text = c.courseid::text
          WHERE b.date_canceled IS NULL
          GROUP BY b.record_id, b.course_id, c.description, c.coursetype, c.bookings_product, b.booking_date, b.start_time, b.end_time, br.record_id, r.record_id, r.max_course_reservations) resource
     LEFT JOIN ( SELECT res1.resource_record_id,
            b1.course_id,
            b1.record_id AS reservation_booking_record_id,
            br1.bookings_resources_record_id,
            count(res1.resource_record_id) AS total_reservations_used
           FROM bookings b1
             LEFT JOIN bookings_reservations br1 ON b1.record_id = br1.bookings_record_id
             JOIN bookings_resources res1 ON br1.bookings_resources_record_id = res1.record_id
          WHERE br1.date_canceled IS NULL
          GROUP BY res1.resource_record_id, b1.course_id, b1.record_id, br1.bookings_resources_record_id) reservation ON resource.bookings_resources_record_id = reservation.bookings_resources_record_id
  WHERE resource.coursetype::text <> 'WB'::text AND resource.bookings_product = (-1)
  ORDER BY resource.resource_record_id, resource.course_id, resource.booking_record_id;

ALTER TABLE cvw_bookings_resource_usage_by_booking
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_resource_usage_by_booking
  IS 'Used for the monthly calendar display and for filling bookings.

	Written by Patrick Headley, Linx Consulting, Inc.

	Since courses can have multiple reservations per resource, i.e) a single boat can hold 4 people,
	this view returns the number of available reservations per booking. The value returned is based
	on the number of reservations the resource allows in the course minus the number of
	reservation records created for the course.

	The results are used individually and also by
	grouping the course_id, booking_date and start_time to get a total for all bookings of the 
	same type on the same date/time.

	Excludes canceled bookings.';

CREATE OR REPLACE VIEW cvw_bookings_resource_usage_by_resource_type AS 
 SELECT cvw_bookings_resource_usage_by_booking.location_name,
    cvw_bookings_resource_usage_by_booking.resource_type_code,
    cvw_bookings_resource_usage_by_booking.booking_date,
    cvw_bookings_resource_usage_by_booking.start_time,
    cvw_bookings_resource_usage_by_booking.end_time,
    max(cvw_bookings_resource_usage_by_booking.max_course_reservations) AS max_course_reservations,
    sum(cvw_bookings_resource_usage_by_booking.total_reservations_used) AS total_reservations_used,
        CASE
            WHEN COALESCE(max(cvw_bookings_resource_usage_by_booking.max_course_reservations)::numeric - sum(cvw_bookings_resource_usage_by_booking.total_reservations_used)) >= 0::numeric THEN COALESCE(max(cvw_bookings_resource_usage_by_booking.max_course_reservations)::numeric - sum(cvw_bookings_resource_usage_by_booking.total_reservations_used))
            ELSE 0::numeric
        END AS total_reservations_available
   FROM cvw_bookings_resource_usage_by_booking
  GROUP BY cvw_bookings_resource_usage_by_booking.location_name, cvw_bookings_resource_usage_by_booking.resource_type_code, cvw_bookings_resource_usage_by_booking.booking_date, cvw_bookings_resource_usage_by_booking.start_time, cvw_bookings_resource_usage_by_booking.end_time;

ALTER TABLE cvw_bookings_resource_usage_by_resource_type
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_resource_usage_by_resource_type
  IS 'Returns fields for bookings that describe the resources by 
  time and location with the maximum number of reservations available
  and the count used. All courses of the same description are grouped
  together. The resources must have the same start and end time, which
  should always be the case because rules in the system dictate that
  the resource cannot be used for two time slots that overlap.';

CREATE OR REPLACE VIEW cvw_bookings_resource_usage_by_course AS 
 SELECT u.course_id,
    u.course_description,
    u.location_name,
    u.resource_type_code,
    u.booking_date,
    u.start_time,
    u.end_time,
    sum(u.total_reservations_available) AS total_reservations_available
   FROM cvw_bookings_resource_usage_by_booking u
  GROUP BY u.course_id, u.course_description, u.location_name, u.resource_type_code, u.booking_date, u.start_time, u.end_time;

ALTER TABLE cvw_bookings_resource_usage_by_course
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_resource_usage_by_course
  IS 'Used for the monthly calendar display.

	Written by Patrick Headley, Linx Consulting, Inc.

	Using the result set from cvw_bookings_resource_usage_by_booking,
	returns the total of available reservations for all courses of the same
	course_id that also have the same booking_date and start_time values.

	Allows displaying a single line in the monthly calendar when there are
	multiples of the same course at the same time.';

CREATE OR REPLACE VIEW cvw_bookings_reservations_enabled AS 
 SELECT b.record_id AS bookings_record_id,
    reservations.record_id AS reservation_record_id,
    resources.resource_record_id,
    b.location_name,
    b.booking_date,
    b.start_time,
    b.end_time
   FROM bookings b
     JOIN courses c ON b.course_id::text = c.courseid::text
     JOIN bookings_reservations reservations ON b.record_id = reservations.bookings_record_id
     JOIN bookings_resources resources ON reservations.bookings_resources_record_id = resources.record_id
  WHERE c.bookings_product = (-1) AND reservations.date_canceled IS NULL AND b.date_canceled IS NULL AND reservations.date_canceled IS NULL;

ALTER TABLE cvw_bookings_reservations_enabled
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_reservations_enabled
  IS 'Returns all active (not canceled) reservations. Use the view by matching
on any of the fields that describe the reservation.
Used by cvw_booking_available_whybuy to determine if an item
does not have an active reservation for anything else.

This view was created to make cvw_booking_available_whybuy easier to read and
troubleshoot but it can be used anywhere you need to find active reservations.
';

CREATE OR REPLACE VIEW cvw_booking_available_whybuy AS 
 SELECT b1.record_id AS booking_record_id,
    resources1.record_id AS bookings_resource_record_id,
    resources1.resource_record_id,
        CASE
            WHEN count(resources.resource_record_id) = 0 AND count(reservations.reservation_record_id) = 0 THEN (-1)
            ELSE 0
        END AS resource_available
   FROM bookings b1
     JOIN courses c1 ON b1.course_id::text = c1.courseid::text
     JOIN bookings_resources resources1 ON b1.record_id = resources1.bookings_record_id
     LEFT JOIN cvw_bookings_resources_courses_enabled resources ON resources1.resource_record_id = resources.resource_record_id AND resources.location_name::text = b1.location_name::text AND resources.booking_date = b1.booking_date AND (resources.start_time >= b1.start_time AND resources.start_time <= b1.end_time OR resources.end_time >= b1.start_time AND resources.end_time <= b1.end_time OR b1.start_time >= resources.start_time AND b1.start_time <= resources.end_time OR b1.end_time >= resources.start_time AND b1.end_time <= resources.end_time)
     LEFT JOIN cvw_bookings_reservations_enabled reservations ON resources1.resource_record_id = reservations.resource_record_id AND reservations.booking_date = b1.booking_date AND (reservations.start_time >= b1.start_time AND reservations.start_time <= b1.end_time OR reservations.end_time >= b1.start_time AND reservations.end_time <= b1.end_time OR b1.start_time >= reservations.start_time AND b1.start_time <= reservations.end_time OR b1.end_time >= reservations.start_time AND b1.end_time <= reservations.end_time)
  WHERE b1.date_canceled IS NULL AND c1.coursetype::text = 'WB'::text AND c1.bookings_product = (-1)
  GROUP BY b1.record_id, resources1.record_id, resources1.resource_record_id;

ALTER TABLE cvw_booking_available_whybuy
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_booking_available_whybuy
  IS 'This view returns -1 if the booking resource is available for a whybuy booking.
  It returns 0 if not.
  A booking resource is available for a whybuy reservation if it is not canceled, not assigned to any
  COURSE booking resource and has no reservations that have not been canceled.';

CREATE OR REPLACE VIEW cvw_bookings_cancellation_message AS 
 SELECT br.record_id,
    b.booking_date,
    b.start_time,
    b.location_name,
    c.description AS course_description
   FROM bookings_reservations br
     JOIN bookings b ON br.bookings_record_id = b.record_id
     JOIN courses c ON b.course_id::text = c.courseid::text;

ALTER TABLE cvw_bookings_cancellation_message
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_cancellation_message
  IS 'This view is used by the booking site when cancelling a reservation to
	display the details of the booking in the cancallation dialog.';

CREATE OR REPLACE VIEW cvw_bookings_reservations_by_group_id AS 
 SELECT wbm.groupid AS group_id,
    b.course_id,
    b.location_name,
    b.booking_date
   FROM whybuymembership wbm
     JOIN bookings_reservations br ON wbm.peopleid = br.people_id
     JOIN bookings b ON br.bookings_record_id = b.record_id
     JOIN courses c ON b.course_id::text = c.courseid::text
  WHERE br.date_canceled IS NULL AND c.coursetype::text = 'WB'::text
  GROUP BY wbm.groupid, b.course_id, b.location_name, b.booking_date;

ALTER TABLE cvw_bookings_reservations_by_group_id
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_reservations_by_group_id
  IS 'This view returns booking information for reservations
	made by whybuy group ids. It is used by 
	cvw_bookings_reservations_by_group_id() to determine if 
	anyone in the whybuy group created a reservation on a
	specific date.';

CREATE OR REPLACE VIEW cvw_bookings_courses_by_email AS 
 SELECT DISTINCT e.emailaddress,
    ct.courseid,
    c.description AS course_description,
    c.displayorder
   FROM coursestaken ct
     JOIN emails e ON ct.peopleid = e.peopleid
     JOIN courses c ON ct.courseid::text = c.courseid::text
  WHERE (c.coursetype::text = ANY (ARRAY['COURSE'::text, 'WB'::text])) AND c.bookings_product = (-1);

ALTER TABLE cvw_bookings_courses_by_email
  OWNER TO "vicsailBE";
COMMENT ON VIEW cvw_bookings_courses_by_email
  IS 'Returns coursestaken records related to a user''s email address. 
  The view is used by the booking system to return
  the user''s coursestaken information regardless of which email address the user logged in with.';


COMMIT TRANSACTION;

